package com.kgc.dao;

import com.kgc.entity.Room;
import com.kgc.entity.UserRoom;
import com.kgc.untils.PageUtils;
import org.apache.commons.lang3.StringUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class AdminRoomDao extends BaseDao {
    public List<Room> selectRoom(PageUtils pageUtils, String status) {
        List<Room> list = new ArrayList<>();
        try {
            String sql = "SELECT r.*,d.name as danyuanName FROM room r Left JOIN danyuan d on r.danyuan_id = d.id ";
            if (StringUtils.isNotEmpty(status)) {
                sql += " where status=" + status;
            }
            sql += " limit ?,?";
            ResultSet rs = super.getExecuteQuery(sql, pageUtils.start(), pageUtils.getPageSize());
            while (rs.next()) {
                list.add(new Room(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getInt(5), rs.getString(6)));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return list;
    }

    public int totalRoom(String name) {
        try {
            String sql = "select count(*) from  room ";
            if (StringUtils.isNotEmpty(name)) {
                sql += " where name like '%" + name + "%'";
            }
            ResultSet rs = super.getExecuteQuery(sql);
            while (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return 0;
    }

    public int addRoom(Room room) {
        return super.getExecuteUpdate("INSERT INTO `wyadmin10`.`room`(`name`, `area`, `status`, `danyuan_id`) VALUES ( ?, ?, 0, ?);\n;"
                , room.getName(), room.getArea(), room.getDanyuanName());
    }

    public int deleteRoom(String id) {
        return super.getExecuteUpdate("delete from room where id=?", id);
    }

    public int updateroom(Room room) {
        String sql = "UPDATE `room` SET `name` = ?, `area` = ? ,danyuan_id = ? WHERE `id` = ?";
        return super.getExecuteUpdate(sql, room.getName(), room.getArea(), room.getDanyuanId(), room.getId());
    }


    public List<UserRoom> selectUserRoom(PageUtils pageUtils, Integer id) {
        List<UserRoom> list = new ArrayList<>();
        try {
            String sql = "SELECT r.id,u.id,u.username,ur.inTime,ur.outTime FROM user_room ur INNER JOIN room r on ur.room_id =r.id INNER JOIN user u ON u.id = ur.user_id WHERE r.id = ? limit ?,?";
            ResultSet rs = super.getExecuteQuery(sql, id, pageUtils.start(), pageUtils.getPageSize());
            while (rs.next()) {
                list.add(new UserRoom(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getTimestamp(4),
                        rs.getTimestamp(5)));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return list;
    }

    public int totaluserRoom(Integer id) {
        String sql = "SELECT count(*) FROM user_room ur INNER JOIN room r on ur.room_id =r.id INNER JOIN user u ON u.id = ur.user_id WHERE r.id = ? ";
        try {
            ResultSet rs = super.getExecuteQuery(sql, id);
            while (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return 0;
    }

    /**
     * 查询房屋使用者
     *
     * @param userId
     * @return roomName List
     */
    public List<UserRoom> selectRoomByUserId(Integer userId) {
        List<UserRoom> list = new ArrayList<>();
        try {
            String sql = "select ur.*,CONCAT(d.name,r.name) from user_room ur inner join room r on ur.room_id = r.id inner join danyuan d on d.id = r.danyuan_id where  ur.user_id=? and  ur.status = 1";
            ResultSet rs = super.getExecuteQuery(sql, userId);
            while (rs.next()) {
                list.add(new UserRoom(rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getTimestamp(4), rs.getTimestamp(5),null, rs.getString(7)));

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return list;
    }

    /**
     * 根据ID查询房屋使用者
     * @param id
     * @return
     */
    public UserRoom selectUserRoomById(Integer id) {
        try {
            String sql = "select * from user_room   where id = ?";
            ResultSet rs = super.getExecuteQuery(sql, id);
            while (rs.next()) {
                return new UserRoom(rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getTimestamp(4), rs.getTimestamp(5));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return null;
    }

    /**
     * 修改user_room状态
     * @param id
     * @return
     */
    public int updateRoomUserStatus(int id) {
        String sql = "UPDATE `user_room` SET `outTime` = now(), `status` = 0 WHERE `id` = ?";
        return super.getExecuteUpdate(sql, id);
    }

    /**
     * 修改room状态
     * @param id
     * @param status
     * @return
     */
    public int updateRoomStatus(Integer id, Integer status) {
        String sql = "UPDATE `room` SET `status` = ? WHERE `id` = ?";
        return super.getExecuteUpdate(sql, status, id);
    }

    /**
     * 查询有房屋分配单元列表
     * @param did
     * @return
     */
    public List<Room> selectRoomByDanYuan(int did) {
        List<Room> list = new ArrayList<>();
        try {
            String sql = "select * from room  where danyuan_id = ? and status = 0 ";
            ResultSet rs = super.getExecuteQuery(sql, did);
            while (rs.next()) {
                list.add(new Room(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getInt(5)));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return list;
    }

    /**
     * 添加用户房屋
     * @param userRoom
     * @return
     */
    public int addAdminRoom(UserRoom userRoom) {
        String sql = "INSERT INTO `user_room`( `user_id`, `room_id`, `inTime`, `outTime`, `status`) VALUES (?, ?, now(), null, 1)";
        return super.getExecuteUpdate(sql, userRoom.getUserId(), userRoom.getRoomId());

    }
}
